<?php
include 'CategoryReferenceLogic.php';
include 'TypeReferenceLogic.php';
include_once 'CreatorLogic.php';
include_once 'SpecimenLogic.php';
include_once 'SpeciesLogic.php';
class ReferenceLogic {
    public function filter($filter) {
    	                		
        $c = array();
        $rs = array();
        // where de cada entidade com OR entre
        $creatorWhere = '';
        $titleWhere = '';
        $typeReferenceWhere = '';

        //Filter already related References
        $relatedRefWhere = '';

        if($filter['list']!=null) {     
            foreach ($filter['list'] as &$v) {
                if($v['controller']=='title') {
                    $titleWhere = $titleWhere==''?'':$titleWhere.' OR ';
                    $titleWhere = $titleWhere.' refview.title ilike \'%'.$v['id'].'%\''.' OR difference(refview.title, \''.$v['id'].'\') > 3';                
	            }    //if($v['controller']=='creator') {
	                //    $creatorWhere = $creatorWhere==''?'':$creatorWhere.' OR ';
	                //    $creatorWhere = $creatorWhere.' refview.idcreator = '.$v['id'];
	               // }
                if($v['controller']=='typereference') {
                    	$typeReferenceWhere = $typeReferenceWhere==''?'':$typeReferenceWhere.' OR ';
                    	$typeReferenceWhere = $typeReferenceWhere.' refview.idtypereference = '.$v['id'];                
            	}
        	}
        }

        if($filter['refFilterList'] != null){
            foreach ($filter['refFilterList'] as $key => $value){
                $relatedRefWhere = $relatedRefWhere==''?'':$relatedRefWhere.' AND ';
                $relatedRefWhere = $relatedRefWhere.' refview.idreferenceelement <> '.$value;
            }
        }

        // se o where de cada entidades nao estiver vazias, coloca AND antes
        $titleWhere = $titleWhere!=''?' AND ('.$titleWhere.') ':'';
        $typeReferenceWhere = $typeReferenceWhere!=''?' AND ('.$typeReferenceWhere.') ':'';
        //$creatorWhere = $creatorWhere!=''?' AND ('.$creatorWhere.') ':'';
        $relatedRefWhere = $relatedRefWhere!=''?' AND ('.$relatedRefWhere.') ':'';

        // parametros da consulta
        //$c['select'] = 'SELECT distinct ON (ref.idreferenceelement) ref.idreferenceelement, ref.isrestricted, ref.title, ref.source, cr.categoryreference, typereference.typereference, creator.creator, scr.subcategoryreference, file.filesystemname, file.path ';
        //$c['from'] = ' FROM referenceelement as ref ';
        //$c['select'] = 'SELECT distinct ON (refview.idreferenceelement) *';
        $c['select'] = 'SELECT * ';
        $c['from'] = ' FROM reference_view refview ';
        //$c['join'] = $c['join'].' LEFT JOIN categoryreference cr ON ref.idcategoryreference = cr.idcategoryreference ';
        //$c['join'] = $c['join'].' LEFT JOIN subcategoryreference scr ON ref.idsubcategoryreference = scr.idsubcategoryreference ';
        //$c['join'] = $c['join'].' LEFT JOIN typereference ON ref.idtypereference = typereference.idtypereference ';
        //$c['join'] = $c['join'].' LEFT JOIN referencecreator refcre ON ref.idreferenceelement = refcre.idreferenceelement ';
        //$c['join'] = $c['join'].' LEFT JOIN creator ON refcre.idcreator = creator.idcreator ';
        //$c['join'] = $c['join'].' LEFT JOIN file ON ref.idfile = file.idfile ';

        $c['where'] = ' WHERE 1 = 1 '.$titleWhere.$typeReferenceWhere.$creatorWhere.$relatedRefWhere;
        $c['orderby'] = ' ORDER BY refview.title ';
        $c['limit'] = ' limit '.$filter['limit'];
        $c['offset'] = ' offset '.$filter['offset'];
        // junta tudo
        $sql = $c['select'].$c['from'].$c['join'].$c['where'].$c['orderby'].$c['limit'].$c['offset'];

        // faz consulta e manda para list
        //echo 
        //$sql = 'select * from ('.$sql.') as a order by a.title';
        $rs['list'] = WebbeeController::executaSQL($sql);
        // altera parametros de consulta para fazer o Count
        //$c['select'] = 'SELECT count(*) ';
        $sql = 'SELECT COUNT(*) FROM ('.$c['select'].$c['from'].$c['join'].$c['where'].') as a';
        // faz consulta do Count e manda para count
        //echo $sql;die();
        $rs['count'] = WebbeeController::executaSQL($sql);
        return $rs;
    
    }
    public function showReference ($filter)
    {
        $showReferenceWhere = '';

        if($filter['refShowList'] != null)
        {
            foreach ($filter['refShowList'] as $key => $value)
            {
                $showReferenceWhere = $showReferenceWhere==''?'':$showReferenceWhere.' OR ';
                $showReferenceWhere = $showReferenceWhere.' ref.idreferenceelement = '.$value;
            }
        }

        else
            $showReferenceWhere = ' ref.idreferenceelement = 0';

        $showReferenceWhere = $showReferenceWhere!=''?' AND ('.$showReferenceWhere.') ':'';
        // parametros da consulta
        $c['select'] = 'SELECT ref.idreferenceelement, ref.isrestricted, ref.title, typereference.typereference, file.filesystemname, file.path ';
        $c['from'] = ' FROM referenceelement ref ';
        $c['join'] = $c['join'].' LEFT JOIN typereference ON ref.idtypereference = typereference.idtypereference ';
        $c['join'] = $c['join'].' LEFT JOIN file ON ref.idfile = file.idfile ';
        $c['where'] = ' WHERE 1 = 1 '.$showReferenceWhere;
        $c['orderby'] = ' ORDER BY ref.title ';
        // junta tudo
        $sql = $c['select'].$c['from'].$c['join'].$c['where'].$c['orderby'];
        // faz consulta e manda para list
        //echo $sql;die();
        $rs['list'] = WebbeeController::executaSQL($sql);
        // altera parametros de consulta para fazer o Count
        $c['select'] = 'SELECT count(*) ';
        $sql = $c['select'].$c['from'].$c['join'].$c['where'];
        // faz consulta do Count e manda para count
        $rs['count'] = WebbeeController::executaSQL($sql);
        return $rs;

    }
    public function search($q) {

        /*
        $creatorLogic = new CreatorLogic();
        $creatorList = $creatorLogic->search($q);
        $typeReferenceLogic = new TypeReferenceLogic();
        $typeReferenceList = $typeReferenceLogic->search($q);
        $categoryReferenceLogic = new CategoryReferenceLogic();
        $categoryReferenceList = $categoryReferenceLogic->search($q);*/


        //Temporary - need to change search function - using searchList for now
        //$creatorLogic = new CreatorLogic();
        //$creatorList = $creatorLogic->searchList($q);
        $typeReferenceLogic = new TypeReferenceLogic();
        $typeReferenceList = $typeReferenceLogic->search($q);


        $rs = array();

        $rs[] = array("controller" => "title","id" => $q,"label" => $q, "category" => "Title");

        foreach($typeReferenceList as $n=>$ar) {
            $rs[] = array("controller" => "typereference","id" => $ar->idtypereference,"label" => $ar->typereference,"category" => "Type");
        }
        //foreach($creatorList as $n=>$ar) {
        //    $rs[] = array("controller" => "creator","id" => $ar->idcreator,"label" => $ar->creator,"category" => "Creator");
        //}
        return $rs;
    }
    public function save($ar) {
        $ar->modified=date('Y-m-d G:i:s');

        $rs = array ();
        if($ar->validate()) {
            $rs['success'] = true;
            $rs['operation'] = $ar->idreferenceelement == null?'created':'updated';
            $ar->setIsNewRecord($rs['operation']=='created');
            $aux = array();
            $aux[] = 'Successfully '.$rs['operation'].' reference record titled <b>'.$ar->title.' </b> ';
            $rs['msg'] = $aux;
            $ar->idreferenceelement = $ar->getIsNewRecord()?null:$ar->idreferenceelement;
            $ar->save();
            $rs['id'] = $ar->idreferenceelement;
            return $rs;
        }else {
            $erros = array ();
            foreach($ar->getErrors() as $n=>$mensagem):
                if($mensagem[0]!="") {
                    $erros[] = $mensagem[0];
            }
            endforeach;
            $rs['success'] = false;
            $rs['msg'] = $erros;
            return $rs;
        }
    }
    public function delete($id) {
        $ar = ReferenceAR::model();
        $ar = $ar->findByPk($id);
        $l = new CreatorLogic();
        $l->deleteReferenceRecord($id);
        $l = new SpecimenLogic();
        $l->deleteReference($id);
        $l = new SpeciesLogic();
        $l->deleteReference($id);
        $ar->delete();
    }
    public function fillDependency($ar) {
        //if($ar->file==null)
        //    $ar->file = FileAR::model();
        //if($ar->fileformat==null)
        //    $ar->fileformat = FileFormatAR::model();

        return $ar;
    }
    public function saveSpecimenNN($idreference, $idspecimen) {
        if(SpecimenReferenceAR::model()->find(" idspecimen=$idspecimen AND idreferenceelement=$idreference ")==null) {
            $ar = SpecimenReferenceAR::model();
            $ar->idspecimen = $idspecimen;
            $ar->idreferenceelement = $idreference;
            $ar->setIsNewRecord(true);
            $ar->save(false);
        }
    }
    public function deleteSpecimenNN($idreference, $idspecimen) {
        $ar = SpecimenReferenceAR::model();
        $ar = $ar->find(" idspecimen=$idspecimen AND idreferenceelement=$idreference ");
        if($ar!=null) {
            $ar->delete();
        }
    }
    public function deleteSpecimen($idspecimen) {
        $ar = SpecimenReferenceAR::model();
        $arList = $ar->findAll(" idspecimen=$idspecimen ");
        foreach ($arList as $n=>$ar)
            $ar->delete();
    }
    public function saveSpeciesNN($idreference, $idspecies) {
        if(SpeciesReferenceAR::model()->find(" idspecies=$idspecies AND idreferenceelement=$idreference ")==null) {
            $ar = SpeciesReferenceAR::model();
            $ar->idspecies = $idspecies;
            $ar->idreferenceelement = $idreference;
            $ar->setIsNewRecord(true);
            $ar->save(false);
        }
    }
    public function deleteSpeciesNN($idreference, $idspecies) {
        $ar = SpeciesReferenceAR::model();
        $ar = $ar->find(" idspecies=$idspecies AND idreferenceelement=$idreference ");
        if($ar!=null) {
            $ar->delete();
        }
    }
    public function deleteSpecies($idspecies) {
        $ar = SpeciesReferenceAR::model();
        $arList = $ar->findAll(" idspecies=$idspecies ");
        foreach ($arList as $n=>$ar)
            $ar->delete();
    }
    public function getTip($filter)
    {
        $c = array();
        $rs = array();

        // parametros da consulta
        $c['select'] = 'SELECT *  ';
        $c['from'] = ' FROM referenceelement ref ';
        //$c['join'] = $c['join'].' LEFT JOIN typereference ON ref.idtypereference = typereference.idtypereference ';
        //$c['join'] = $c['join'].' LEFT JOIN referencecreator refcre ON ref.idreferenceelement = refcre.idreferenceelement ';
        //$c['join'] = $c['join'].' LEFT JOIN creator ON refcre.idcreator = creator.idcreator ';
        //$c['join'] = $c['join'].' LEFT JOIN file ON ref.idfile = file.idfile ';

        $c['where'] = ' WHERE ref.idreferenceelement = '.$filter['idreference'];
        // junta tudo
        $sql = $c['select'].$c['from'].$c['join'].$c['where'];

        $rs = WebbeeController::executaSQL($sql);

        return $rs;
    }
}
?>
